package com.stony.orc;

import org.h2.jdbcx.JdbcConnectionPool;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import javax.sql.DataSource;
import java.io.IOException;
import java.math.BigDecimal;
import java.sql.*;
import java.util.*;
import java.util.Date;

/**
 * <p>orcTest
 * <p>com.stony.orc
 *
 * @author stony
 * @version 下午6:04
 * @since 2017/11/16
 */
public class Store implements AutoCloseable {

    private static final Logger logger = LoggerFactory.getLogger(Store.class);
    final static String url = "jdbc:h2:~/.h2/orc;ACCESS_MODE_DATA=rws;MODE=MySQL;AUTO_SERVER=TRUE";
    final DataSource dataSource;
    ThreadLocal<Connection> connectionThreadLocal = new ThreadLocal<>();

    public Store(DataSource dataSource) {
        this.dataSource = dataSource;
    }

    public static Store newStore() {
        return new Store(JdbcConnectionPoolHolder.getPool());
    }

    public VisitRecord getVisitRecord(String userName, Date dateTime) {
        Connection conn = getConnection();
        String sql = "SELECT ID,PRICE,DATE_TIME,USER_NAME,DOCTOR,HOSPITAL_NUMBER,TONIC,TONIC_NUMBER,AGE,SEX,UPDATE_TIME \n" +
                "FROM visit_record  \n" +
                "WHERE user_name = ? AND date_time = ?";
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            int index = 1;
            ps = conn.prepareStatement(sql);
            ps.setString(index++, userName);
            ps.setTimestamp(index++, new Timestamp(dateTime.getTime()));
            rs = ps.executeQuery();
            if (rs.next()) {
                return mapperServiceInfo(rs);
            }
        } catch (SQLException e) {
            String msg = "\n" + sql + "\n获取" + userName + "记录" + dateTime + "异常: \n";
            logger.error(msg, e);
        } finally {
            close(rs);
            close(ps);
        }
        return null;
    }
    public List<VisitRecord> getVisitRecordAll() {
        Connection conn = getConnection();
        String sql = "SELECT ID,PRICE,DATE_TIME,USER_NAME,DOCTOR,HOSPITAL_NUMBER,TONIC,TONIC_NUMBER,AGE,SEX,UPDATE_TIME \n" +
                "FROM visit_record  \n";
        PreparedStatement ps = null;
        ResultSet rs = null;
        List<VisitRecord> list = new ArrayList<>();
        try {
            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery();
            while (rs.next()) {
                list.add(mapperServiceInfo(rs));
            }
        } catch (SQLException e) {
            String msg = "\n" + sql + "\n获取全部记录异常: \n";
            logger.error(msg, e);
        } finally {
            close(rs);
            close(ps);
        }
        return list;
    }
    VisitRecord mapperServiceInfo(ResultSet rs) throws SQLException{
        if(rs == null) return null;
        VisitRecord serviceInfo = new VisitRecord();
        serviceInfo.setId(rs.getLong("ID"));
        serviceInfo.setPrice(rs.getBigDecimal("PRICE"));
        serviceInfo.setDateTime(rs.getTimestamp("DATE_TIME"));
        serviceInfo.setUpdateTime(rs.getTimestamp("UPDATE_TIME"));
        serviceInfo.setUserName(rs.getString("USER_NAME"));
        serviceInfo.setDoctor(rs.getString("DOCTOR"));
        serviceInfo.setSex(rs.getString("SEX"));
        serviceInfo.setTonic(rs.getString("TONIC"));
        serviceInfo.setHospitalNumber(rs.getLong("HOSPITAL_NUMBER"));
        serviceInfo.setTonicNumber(rs.getLong("TONIC_NUMBER"));
        serviceInfo.setAge(rs.getInt("AGE"));
        return serviceInfo;
    }

    /**
     *
     * @param visitRecord
     * @return 大于 0 is id, 等于 0, 小于 0 is error
     */
    public long saveVisitRecord(VisitRecord visitRecord) {
        Connection conn = getConnection();
        VisitRecord visit = getVisitRecord(visitRecord.getUserName(), visitRecord.getDateTime());
        if(visit != null){
            visitRecord.setId(visit.getId());
            visitRecord.setUpdateTime(new Timestamp(System.currentTimeMillis()));
            return updateVisitRecord(visitRecord);
        }
        String sql = "INSERT INTO visit_record (\n" +
                "PRICE,DATE_TIME,USER_NAME,DOCTOR,HOSPITAL_NUMBER,TONIC,TONIC_NUMBER,AGE,SEX,UPDATE_TIME \n" +
                ") VALUES (?,?,?, ?,?,?, ?,?,?, ?)";
        PreparedStatement ps = null;
        try {
            int index = 1;
            ps = conn.prepareStatement(sql);

            ps.setBigDecimal(index++, visitRecord.getPrice());
            ps.setTimestamp(index++, visitRecord.getDateTime());
            ps.setString(index++, visitRecord.getUserName());
            ps.setString(index++, visitRecord.getDoctor());
            ps.setLong(index++, visitRecord.getHospitalNumber());
            ps.setString(index++, visitRecord.getTonicJson());
            ps.setLong(index++, visitRecord.getTonicNumber());
            ps.setInt(index++, visitRecord.getAge());
            ps.setString(index++, visitRecord.getSex());
            ps.setTimestamp(index++, new Timestamp(System.currentTimeMillis()));
            ps.executeUpdate();
            ResultSet rs = ps.getGeneratedKeys();
            if (rs.next()) {
                long id =  rs.getLong(1);
                if(id >= 1L && visitRecord.getTonicList() != null) {
                    saveVisitRecord(id, visitRecord.getTonicList());
                    return id;
                }
            }
        } catch (SQLException e) {
            String msg = "\n" + sql + "\n保存记录[" + visitRecord + "]异常: \n";
            logger.error(msg, e);
            return -1;
        } finally {
            close(ps);
        }
        return 0;
    }

    /**
     *
     * @param visitRecord
     * @return 大于 0 is id, 等于 0, 小于 0 is error
     */
    private long updateVisitRecord(VisitRecord visitRecord) {
        Connection conn = getConnection();
        String sql = "UPDATE visit_record \n" +
                "SET PRICE = ?, DOCTOR = ?, HOSPITAL_NUMBER = ?, TONIC = ?, \n" +
                "TONIC_NUMBER= ?, AGE = ?, UPDATE_TIME = ? \n" +
                "WHERE USER_NAME = ? and DATE_TIME = ?";
        PreparedStatement ps = null;
        try {
            int index = 1;
            ps = conn.prepareStatement(sql);
            ps.setBigDecimal(index++, visitRecord.getPrice());
            ps.setString(index++, visitRecord.getDoctor());
            ps.setLong(index++, visitRecord.getHospitalNumber());
            ps.setString(index++, visitRecord.getTonicJson());
            ps.setLong(index++, visitRecord.getTonicNumber());
            ps.setInt(index++, visitRecord.getAge());
            ps.setTimestamp(index++, new Timestamp(System.currentTimeMillis()));
            ps.setString(index++, visitRecord.getUserName());
            ps.setTimestamp(index++, visitRecord.getDateTime());
            ps.executeUpdate();
            return visitRecord.getId();
        } catch (SQLException e) {
            String msg = "\n" + sql + "\n更新记录["+ visitRecord + "]异常: \n";
            logger.error(msg, e);
            return -1;
        } finally {
            close(ps);
        }
    }
    public void delVisitRecord(long visitId) {
        Connection conn = getConnection();
        String sql = "DELETE tonic_record WHERE visit_id = ?";
        PreparedStatement ps = null;
        try {
            ps = conn.prepareStatement(sql);
            ps.setLong(1, visitId);
            int size = ps.executeUpdate();
            logger.info("批量删除记录{}条", size);
        } catch (SQLException e) {
            String msg = "\n" + sql + "\n删除TonicList记录[" + visitId + "]异常: \n";
            logger.error(msg, e);
        } finally {
            close(ps);
        }
    }
    public void saveVisitRecord(long visitId, List<Tonic> tonicList) {
        if (!tonicList.isEmpty()) {
            Connection conn = getConnection();
            String sql = "INSERT INTO tonic_record (\n" +
                    "VISIT_ID, TONIC_WEIGHT, TONIC_NAME \n" +
                    ") VALUES (?,?,?)";
            PreparedStatement ps = null;
            try {
                delVisitRecord(visitId);
                int index = 1;
                ps = conn.prepareStatement(sql);
                for(Tonic tonic : tonicList) {
                    ps.setLong(index++, visitId);
                    ps.setBigDecimal(index++, new BigDecimal(tonic.getWeight()));
                    ps.setString(index++, tonic.getName());
                    ps.addBatch();
                    index = 1;
                }
                int[] batch = ps.executeBatch();
                logger.info("批量更新记录{}条", batch.length);
            } catch (SQLException e) {
                String msg = "\n" + sql + "\n保存TonicList记录[" + tonicList + "]异常: \n";
                logger.error(msg, e);
            } finally {
                close(ps);
            }
        }
    }
    public void updateTonicPageRecord(TonicPageRecord record) {
        if (record != null) {
            Connection conn = getConnection();
            String sql = "UPDATE tonic_page_record  \n" +
                    "SET TONIC_NAME = ?, STATUS = ? \n" +
                    "WHERE TONIC_URL = ?";
            PreparedStatement ps = null;
            try {
                int index = 1;
                ps = conn.prepareStatement(sql);
                ps.setString(index++, record.getTonicName());
                ps.setInt(index++, record.getStatus());
                ps.setString(index++, record.getTonicUrl());
                ps.executeUpdate();
                ResultSet rs = ps.getGeneratedKeys();
                if (rs.next()) {
                    long id = rs.getLong(1);
                    logger.info("更新page记录 id={}", id);
                }
            } catch (SQLException e) {
                String msg = "\n" + sql + "\nupdateTonicPageRecord[" + record + "]异常: \n";
                logger.error(msg, e);
            } finally {
                close(ps);
            }
        }
    }
    public void saveTonicPageRecord(TonicPageRecord record) {
        if (record != null) {
            Connection conn = getConnection();
            String sql = "INSERT INTO tonic_page_record (\n" +
                    "TONIC_URL, TONIC_NAME, STATUS \n" +
                    ") VALUES (?,?,?)";
            PreparedStatement ps = null;
            try {
                int index = 1;
                ps = conn.prepareStatement(sql);
                ps.setString(index++, record.getTonicUrl());
                ps.setString(index++, record.getTonicName());
                ps.setInt(index++, record.getStatus());
                ps.executeUpdate();
                ResultSet rs = ps.getGeneratedKeys();
                if (rs.next()) {
                    long id = rs.getLong(1);
                    logger.info("保存TonicPageRecord记录 id={}", id);
                }
            } catch (SQLException e) {
                String msg = "\n" + sql + "\nsaveTonicPageRecord[" + record + "]异常: \n";
                logger.error(msg, e);
            } finally {
                close(ps);
            }
        }
    }
    public long saveTonicDetail(TonicDetail tonicDetail) {
        if (tonicDetail != null) {
            Connection conn = getConnection();
            String sql = "INSERT INTO TONIC_DETAIL (\n" +
                    "TONIC_NAME ,CLASSIFY ,TASTE ,MERIDIAN ,FEATURES ,AVOID ,INGREDIENT ,SOURCE ,UPDATE_TIME \n" +
                    ") VALUES (?,?,?,?,?,?,?,?,?)";
            PreparedStatement ps = null;
            try {
                int index = 1;
                ps = conn.prepareStatement(sql);
                ps.setString(index++, tonicDetail.getTonicName());
                ps.setString(index++, tonicDetail.getClassify());
                ps.setString(index++, tonicDetail.getTaste());
                ps.setString(index++, tonicDetail.getMeridian());
                ps.setString(index++, tonicDetail.getFeatures());
                ps.setString(index++, tonicDetail.getAvoid());
                ps.setString(index++, tonicDetail.getIngredient());
                ps.setString(index++, tonicDetail.getSource());
                ps.setTimestamp(index++, new Timestamp(System.currentTimeMillis()));
                ps.executeUpdate();
                ResultSet rs = ps.getGeneratedKeys();
                if (rs.next()) {
                    long id = rs.getLong(1);
                    logger.info("保存TonicDetail记录 id={}", id);
                    if(id >= 1L) {
                        return id;
                    }
                }
            } catch (SQLException e) {
                String msg = "\n" + sql + "\nsaveTonicDetail[" + tonicDetail + "]异常: \n";
                logger.error(msg, e);
                return -1;
            } finally {
                close(ps);
            }
        }
        return 0;
    }
    void close(PreparedStatement ps) {
        if (ps != null) {
            try {
                ps.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    void close(ResultSet rs) {
        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    public Connection getConnection() {
        Connection conn = connectionThreadLocal.get();
        if (conn == null) {
            conn = getConnectionByDS();
            connectionThreadLocal.set(conn);
        }
        return conn;
    }

    public void releaseConnection() {
        Connection conn = connectionThreadLocal.get();
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
                connectionThreadLocal.set(null);
            }
        }
    }

    Connection getConnectionByDS() {
        try {
            return dataSource.getConnection();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    @Override
    public void close() throws IOException {
        JdbcConnectionPoolHolder.close();
    }


    protected static abstract class JdbcConnectionPoolHolder {
        private static JdbcConnectionPool cp;
        private static final Object monitor = new Object();
        protected static JdbcConnectionPool getPool() {
            if (cp == null) {
                synchronized (monitor) {
                    if (cp == null) {
                        cp = JdbcConnectionPool.create(url, "sa", "");
                    }
                }
            }
            return cp;
        }
        public static void close() throws IOException {
            if (cp != null) {
                cp.dispose();
            }
        }
    }
}
